# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
import psycopg2


class CeeScorePipeline(object):
    def __init__(self, pg_database, pg_user, pg_password, pg_host, pg_port):
        self.database = pg_database
        self.user = pg_user
        self.password = pg_password
        self.host = pg_host
        self.port = pg_port

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            pg_database = crawler.settings.get('PG_DATABASE'),
            pg_user = crawler.settings.get('PG_USER'),
            pg_password = crawler.settings.get('PG_PASSWORD'),
            pg_host = crawler.settings.get('PG_HOST'),
            pg_port = crawler.settings.get('PG_PORT')
        )

    def open_spider(self, spider):
        self.conn = psycopg2.connect(database=self.database, user=self.user, password=self.password, host=self.host,
                               port=self.port)
        self.cur = self.conn.cursor()

    def close_spider(self, spider):
        self.cur.close()
        self.conn.commit()
        self.conn.close()

    def process_item(self, item, spider):
        min = item['min']
        if type(min)==list:
            if len(min)>0:
                minval = str(min[0])
            else:
                minval = str(0)
        else:
            minval = min

        specialtyname = item['specialtyname']
        if type(specialtyname)==list:
            if len(specialtyname)>0:
                specialtynameval = str(min[0])
            else:
                specialtynameval = '--'
        else:
            specialtynameval = specialtyname

        qry = "Insert Into cee_score(schoolname, specialtyname, localprovince, studenttype, year, batch, var_score, max, min) Values ('"+item['schoolname']+"', '"+specialtynameval+"', '"+item['localprovince']+"', '"+item['studenttype']+"', '"+item['year']+"', '"+item['batch']+"', '"+item['var_score']+"', '"+item['max']+"', '"+minval+"')"
        self.cur.execute(qry)
        return item
